package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据实现层
 * 1.封装sql语句
 * 2.调用jdbc查询
 * 3.封装结果集
 * 4.返回结果
 */
public class UserImpl implements UserDao {
    @Override
    public int register(UserModel userModel) {
//        userModel.getUsername()
        String sql = "insert into user (username,password,sex,email,birthday,height,hobby)" +
                "values('" + userModel.getUsername() + "'," +
                "'" + userModel.getPassword() + "'," +
                "'" + userModel.getSex() + "'," +
                "'" + userModel.getEmail() + "'," +
                "'" + userModel.getBirthday() + "'," +
                "'" + userModel.getHeight() + "'," +
                "'" + userModel.getHobby() + "')";
        return JDBC.update(sql);

    }


    @Override
    public UserModel findByUserName(String username) {
        String sql = "select * from user where username='" + username + "'";
        ResultSet resultSet = JDBC.findAll(sql);
        UserModel userModel = null;
        try {
            while (resultSet.next()) {
                userModel = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userModel;
    }

    @Override
    public UserModel findByUserNameAndPassWord(UserModel userModel) {
        String sql = "select * from user where username='" + userModel.getUsername() + "' and password='" + userModel.getPassword() + "'";
        ResultSet resultSet = JDBC.findAll(sql);
        UserModel model = null;
        try {
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
        String sql = "select * from user  where 1=1";
        if (!userModel.getUsername().equals(""))
            sql += " and username like '%" + userModel.getUsername() + "%' ";
        if (!userModel.getPhone().equals(""))
            sql += " and phone like '%" + userModel.getPhone() + "%'";
        if (!"全部".equals(userModel.getSex())) {
            sql += " and  sex = '" + userModel.getSex() + "'";
        }
        if (userModel.getStartBirthday().equals("") && !userModel.getEndBirthday().equals("")) {
            sql += " and birthday < '" + userModel.getEndBirthday() + "'";
        }
        if (!userModel.getStartBirthday().equals("") && userModel.getEndBirthday().equals("")) {
            sql += " and birthday > '" + userModel.getStartBirthday() + "'";
        }
        if (!userModel.getStartBirthday().equals("") && !userModel.getEndBirthday().equals("")) {
            sql += " and (birthday between '" + userModel.getStartBirthday() + "' and '" + userModel.getEndBirthday() + "')";
        }
        if (userModel.getEnable() != -1) {
            sql += " and enable=" + userModel.getEnable();
        }
        if (!userModel.getHobby().equals("")) {
            String[] hobbys = userModel.getHobby().split(",");
            for (int i = 0; i < hobbys.length; i++) {
                sql += " and find_in_set('" + hobbys[i] + "',hobby)";
            }
        }
        sql += " limit " + (userModel.getPageModel().getCurPage() - 1) * userModel.getPageModel().getPageSize() + "," + userModel.getPageModel().getPageSize();

        System.out.println(sql);
        ResultSet resultSet = JDBC.findAll(sql);
        return setList(resultSet);
    }

    @Override
    public UserModel findById(Integer id) {
        String sql = "select * from user where id=" + id;
        ResultSet resultSet = JDBC.findAll(sql);
        return setList(resultSet).get(0);
    }

    @Override
    public int update(UserModel userModel) {
        String sql = "update user set sex='" + userModel.getSex() + "' where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public int del(Integer id) {
        String sql = "delete from user where id=" + id;
        return JDBC.update(sql);
    }

    @Override
    public int getCount(UserModel userModel) {
        String sql = "select count(id) id from user  where 1=1";
        if (!userModel.getUsername().equals(""))
            sql += " and username like '%" + userModel.getUsername() + "%' ";
        if (!userModel.getPhone().equals(""))
            sql += " and phone like '%" + userModel.getPhone() + "%'";
        if (!"全部".equals(userModel.getSex())) {
            sql += " and  sex = '" + userModel.getSex() + "'";
        }
        if (userModel.getStartBirthday().equals("") && !userModel.getEndBirthday().equals("")) {
            sql += " and birthday < '" + userModel.getEndBirthday() + "'";
        }
        if (!userModel.getStartBirthday().equals("") && userModel.getEndBirthday().equals("")) {
            sql += " and birthday > '" + userModel.getStartBirthday() + "'";
        }
        if (!userModel.getStartBirthday().equals("") && !userModel.getEndBirthday().equals("")) {
            sql += " and (birthday between '" + userModel.getStartBirthday() + "' and '" + userModel.getEndBirthday() + "')";
        }
        if (userModel.getEnable() != -1) {
            sql += " and enable=" + userModel.getEnable();
        }
        if (!userModel.getHobby().equals("")) {
            String[] hobbys = userModel.getHobby().split(",");
            for (int i = 0; i < hobbys.length; i++) {
                sql += " and find_in_set('" + hobbys[i] + "',hobby)";
            }
        }
        ResultSet resultSet = JDBC.findAll(sql);
        int count = 0;
        try {
            while (resultSet.next()) {
                count = resultSet.getInt("id");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    private List<UserModel> setList(ResultSet resultSet) {
        List<UserModel> list = new ArrayList<>();

        try {
            while (resultSet.next()) {
                UserModel model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setSex(resultSet.getString("sex"));
                model.setPhone(resultSet.getString("phone"));
                model.setEmail(resultSet.getString("email"));
                model.setAddress(resultSet.getString("address"));
                model.setCreateTime(resultSet.getString("create_time"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setHeight(resultSet.getString("height"));
                model.setHobby(resultSet.getString("hobby"));
                model.setEnable(resultSet.getInt("enable"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
